﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using CommunitySystem.BO;
using CommunitySystemModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using org.in2bits.MyXls;

namespace CommunitySystem
{
    public class ZhaoLuBO
    {
        #region 职位申请
        /// <summary>
        /// 获得登录人可见的申报的职位--yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public ObservableCollection<ZhaoLuZhiWeiModel> GetZhaoLuZhiWei(string OrgID, int ID, int start, int end, int status, int year, string islocked)
        {
            ObservableCollection<ZhaoLuZhiWeiModel> rights = new ObservableCollection<ZhaoLuZhiWeiModel>();
            string sql = "";
            string sql1 = "";
            string sql2 = "";
            if (ID == 0)
            {
                if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                {
//                    sql =
//                        @"select top " + (end - start) + @" a.* from 
//(select top 100 ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName,o.ParentID,o.ID Orgid1 from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID order by p.orgid ) a where a.ID not   in   
//(select   top " + start + @" ID from ZhaoLuZhiWei  order by orgid ) and
//                     (a.ParentID in (" + OrgID + ") or a.Orgid1 in (" + OrgID + ")) and a.year = " + year;
                    sql1 = string.Format(@"select  ROW_NUMBER() OVER(ORDER BY  p.orgid) Sec,  p.*,o.Name OrgName,o.ParentID,o.ID Orgid1 
from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID
where YEAR={0} and (orgid in ({1}) or ParentID in ({1}))", year, OrgID);
                    sql2 = string.Format(@" select top {2} ROW_NUMBER() OVER(ORDER BY  p.orgid) Sec
from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID
where YEAR={0}  and (orgid in ({1}) or ParentID in ({1}))", year, OrgID, start);
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    if (status != -1)
                    {
                        sql += " and a.status = " + status;
                    }
                    sql += " and a.IsLocked = '" + islocked + "'";

                    sql = string.Format(@"select top {2} * from (
                                {0}
                                )aa
                                 where aa.sec not in 
                                 (
                                {1}
                                 )", sql1, sql2, (end - start));
                }
                else//admin登录的
                {
//                    sql = @"select top " + (end - start) + @" a.* from 
//(select top 100 ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName,o.ParentID,o.ID Orgid1 from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID order by p.orgid ) a where a.ID not   in 
//(select   top " + start + @" ID from ZhaoLuZhiWei  order by orgid )  and a.year = " + year;
                    sql1 = string.Format(@"select  ROW_NUMBER() OVER(ORDER BY  p.orgid) Sec,  p.*,o.Name OrgName,o.ParentID,o.ID Orgid1 
from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID
where YEAR={0}", year);
                    sql2 = string.Format(@" select top {1} ROW_NUMBER() OVER(ORDER BY  p.orgid) Sec
from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID
where YEAR={0} ", year,start);
                    if (status != -1)
                    {
                        sql += " and a.status = " + status;
                    }
                    sql += " and a.IsLocked = '" + islocked + "'";
                    //sql += " order by a.Orgid1";

                    sql = string.Format(@"select top {2} * from (
                                {0}
                                )aa
                                 where aa.sec not in 
                                 (
                                {1}
                                 )", sql1, sql2, (end - start));
                }
            }

            else//修改单个信息的时候用
            {
                sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.ID = {0}", ID);
                if (status != -1)
                {
                    sql += " and p.status = " + status;
                }
                sql += " and p.IsLocked = '" + islocked + "'";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuZhiWeiModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<ZhaoLuZhiWeiModel> GetZhaoLuZhiWeiForMianShi(int year)
        {
            ObservableCollection<ZhaoLuZhiWeiModel> rights = new ObservableCollection<ZhaoLuZhiWeiModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY p.id ) Sec, p.*,o.Name OrgName from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.Year = {0}", year);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuZhiWeiModel.FillEntity(dr));
            }
            return rights;
        }

        public bool DeteleZhaoLuZhiWei(string ID)
        {
            string sql = "delete from ZhaoLuZhiWei where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 确定保存
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool SaveZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 1 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 查看职位未审核的有多少条--yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <returns></returns> 
        public string ChaKanShiFouDouShenHeZhaoLuZhiWei(string OrgID, int year, string islocked)
        {
            string sql = "";
            if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
            {
                sql =
                    @"select count(p.[ID]) Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where  p.status =1 and
                     (o.ParentID in (" +
                    OrgID + ") or o.ID in (" + OrgID + ")) and p.year = " + year;
            }
            else
            {
                sql =
                    @"select count(p.[ID]) Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.status =1 and p.year = " + year;
            }
            sql += " and p.IsLocked = '" + islocked + "'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else return null;
        }

        /// <summary>
        /// yy修改
        /// </summary>
        /// <param name="OrgID"></param>
        /// <param name="ID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int GetZhaoLuZhiWeiCount(string OrgID, int ID, int status, int year, string islocked)
        {
            string sql = "";
            if (ID == 0)
            {
                if (OrgID != "0" && !string.IsNullOrEmpty(OrgID))
                {
                    sql =
                        @"select count(p.[ID])/20 + 1 Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where 1=1 and p.year = " + year + @" and
                     (o.ParentID in (" + OrgID + ") or o.ID in (" + OrgID + ")) ";
                    //if (OrgID == 80 || OrgID == 81 || OrgID == 82 || OrgID == 83 || OrgID == 84)
                    //{
                    //    sql += " and o.parentid = " + OrgID;
                    //}
                    //if (OrgID != 80 && OrgID != 81 && OrgID != 82 && OrgID != 83 && OrgID != 84 && OrgID != 79 && OrgID != 0)
                    //{
                    //    sql += " and o.id = " + OrgID;
                    //}
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                }
                else//admin登录的
                {
                    sql =
                        @"select count(p.[ID])/20 + 1 Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where 1=1 and p.year = " +
                        year;
                    if (status != -1)
                    {
                        sql += " and p.status = " + status;
                    }
                    sql += " and p.IsLocked = '" + islocked + "'";
                }
            }

            else//修改单个信息的时候用
            {
                sql = string.Format(@"select count(p.[ID])/20 + 1 Count from ZhaoLuZhiWei p inner join OrgForHR o on p.OrgID=o.ID where p.ID = {0}", ID);
                if (status != -1)
                {
                    sql += " and p.status = " + status;
                }
                sql += " and p.IsLocked = '" + islocked + "'";
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return Convert.ToInt32(ds.Tables[0].Rows[0]["Count"]);
        }

        /// <summary>
        /// 通过审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CheckZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 3 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 退回
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool TuiZhaoLuZhiWei(string ID, string memo)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 2,CheckMemo = '" + memo + "' where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 发布
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool FaBuZhaoLuZhiWei(string ID, int zhaolujihuaID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 4 where ID in (" + ID + ")";
            foreach (string i in ID.Split(','))
            {
                sql += string.Format(@";INSERT INTO ZhaoLuJiHuaDetail1
 ([ZhaoLuJiHuaID],[ZhaoLuZhiWeiID]) values ('{0}',{1})", zhaolujihuaID, i);
            }

            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 取消审核
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool CancelZhaoLuZhiWei(string ID)
        {
            string sql = "update dbo.ZhaoLuZhiWei set Status = 1 where ID in " + ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool AddZhaoLuZhiWei(ZhaoLuZhiWeiModel item)
        {
            string sql = string.Format(@"INSERT INTO [XuHuiCommunityDB].[dbo].[ZhaoLuZhiWei]
           ([OrgID]
           ,[Department]
           ,[Duty]
           ,[DutyDescription]
           ,[PeopleCount]
           ,[JCGZZDNX]
           ,[ZKDX]
           ,[BenKeZhuanYe]
           ,[YanJiuShengZhuanYe]
           ,[XueLiYaoQiu]
           ,[Political]
           ,[ZYKSKM]
           ,[Other]
           ,[Memo]
           ,[Status]
           ,[CheckMemo]
           ,[XueWeiYaoQiu]
           ,[Year]
           ,[IsLocked],Department_Duty,ZhiWeiDaiMa)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,{14}
           ,'{15}'
           ,'{16}'
           ,{17}
           ,'{18}','{19}','{20}')", item.OrgID, item.Department, item.Duty, item.DutyDescription, item.PeopleCount, item.JCGZZDNX, item.ZKDX, item.BenKeZhuanYe,
                    item.YanJiuShengZhuanYe, item.XueLiYaoQiu, item.Political, item.ZYKSKM, item.Other, item.Memo, item.Status, item.CheckMemo,
                    item.XueWeiYaoQiu, item.Year, item.IsLocked, item.Department_Duty, item.ZhiWeiDaiMa);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool ImportZhaoLuZhiWei(ZhaoLuZhiWeiModel item)
        {
            string sql = string.Format(@"INSERT INTO [XuHuiCommunityDB].[dbo].[ZhaoLuZhiWei]
           ([OrgID]
           ,[Department]
           ,[Duty]
           ,[DutyDescription]
           ,[PeopleCount]
           ,[JCGZZDNX]
           ,[ZKDX]
           ,[BenKeZhuanYe]
           ,[YanJiuShengZhuanYe]
           ,[XueLiYaoQiu]
           ,[Political]
           ,[ZYKSKM]
           ,[Other]
           ,[Memo]
           ,[Status]
           ,[CheckMemo]
           ,[XueWeiYaoQiu]
           ,[Year]
           ,[IsLocked],Department_Duty,ZhiWeiDaiMa)
     VALUES
           ((select ID from OrgForHR where Name='{0}')
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,{14}
           ,'{15}'
           ,'{16}'
           ,{17}
           ,'{18}','{19}','{20}')", item.OrgName, item.Department, item.Duty, item.DutyDescription, item.PeopleCount, item.JCGZZDNX, item.ZKDX, item.BenKeZhuanYe,
                    item.YanJiuShengZhuanYe, item.XueLiYaoQiu, item.Political, item.ZYKSKM, item.Other, item.Memo, item.Status, item.CheckMemo,
                    item.XueWeiYaoQiu, item.Year, item.IsLocked, item.Department_Duty, item.ZhiWeiDaiMa);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool UpdateZhaoLuZhiWei(ZhaoLuZhiWeiModel item)
        {
            string sql = string.Format(@"UPDATE [XuHuiCommunityDB].[dbo].[ZhaoLuZhiWei]
   SET [OrgID] = {0}
      ,[Department] ='{1}'
      ,[Duty] = '{2}'
      ,[DutyDescription] = '{3}'
      ,[PeopleCount] = {4}
      ,[JCGZZDNX] = '{5}'
      ,[ZKDX] = '{6}'
      ,[BenKeZhuanYe] = '{7}'
      ,[YanJiuShengZhuanYe] = '{8}'
      ,[XueLiYaoQiu] = '{9}'
      ,[Political] = '{10}'
      ,[ZYKSKM] = '{11}'
      ,[Other] = '{12}'
      ,[Memo] = '{13}'
      ,[Status] = {14}
      ,[CheckMemo] = '{15}'
      ,[XueWeiYaoQiu] = '{16}'
      ,[Year] = {17}
      ,[IsLocked] = '{18}',Department_Duty = '{19}',ZhiWeiDaiMa = '{20}'
 WHERE ID = {21}", item.OrgID, item.Department, item.Duty, item.DutyDescription, item.PeopleCount, item.JCGZZDNX, item.ZKDX, item.BenKeZhuanYe,
                    item.YanJiuShengZhuanYe, item.XueLiYaoQiu, item.Political, item.ZYKSKM, item.Other, item.Memo, item.Status, item.CheckMemo,
                    item.XueWeiYaoQiu, item.Year, item.IsLocked, item.Department_Duty, item.ZhiWeiDaiMa, item.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        /// <summary>
        /// 锁定本期工作
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool SuoZhaoLuZhiWei()
        {
            //            string sql = @"update dbo.ZhaoLuZhiWei set islocked = 1 where islocked = 0;
            //update ZhaoLuJiHua set IsLocked = 1 where ID in
            //(select ZhaoLuJiHuaID from ZhaoLuJiHuaDetail1 where ZhaoLuZhiWeiID in
            //(select ID from ZhaoLuZhiWei where IsLocked = 0)) ";
            string sql = @"update ZhaoLuZhiWei set islocked = 1 where islocked = 0;
                           update ZhaoLuJiHua set IsLocked = 1 where islocked = 0";
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool JieSuoZhaoLuZhiWei(int id)
        {
            //            string sql = @"update dbo.ZhaoLuZhiWei set islocked = 1 where islocked = 0;
            //update ZhaoLuJiHua set IsLocked = 1 where ID in
            //(select ZhaoLuJiHuaID from ZhaoLuJiHuaDetail1 where ZhaoLuZhiWeiID in
            //(select ID from ZhaoLuZhiWei where IsLocked = 0)) ";
            string sql = string.Format(@"update ZhaoLuZhiWei set islocked = 0 where Year = {0};
                           update ZhaoLuJiHua set IsLocked = 0 where id = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        #endregion

        #region 计划录用管理
        public ObservableCollection<ZhaoLuJiHuaModel> GetZhaoLuJiHuaModel(int zhaoluid, string islocked,int year)
        {
            ObservableCollection<ZhaoLuJiHuaModel> rights = new ObservableCollection<ZhaoLuJiHuaModel>();
            string sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY id ) Sec,* from ZhaoLuJiHua  where 1=1 ");
            if (zhaoluid != 0)
            {
                sql += " and ID = " + zhaoluid;
            }
            if (year != 0)
            {
                sql += " and Year = " + year;
            }
            if (!string.IsNullOrEmpty(islocked))
            {
                sql += " and IsLocked = '" + islocked + "'";
            }
            sql += " order by id desc";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaModel.FillEntity(dr));
            }
            return rights;
        }
        /// <summary>
        /// 用于招录职位的条件上的招录计划名称combobox
        /// </summary>
        /// <param name="zhaoluid"></param>
        /// <param name="islocked"></param>
        /// <returns></returns>
        public ObservableCollection<ZhaoLuJiHuaModel> GetZhaoLuJiHuaModelForCom()
        {
            ObservableCollection<ZhaoLuJiHuaModel> rights = new ObservableCollection<ZhaoLuJiHuaModel>();
            string sql = string.Format(@"select * from zhaolujihua where islocked = 0
union all
select * from zhaolujihua where islocked = 1 order by id desc");
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<ZhaoLuJiHuaDetailModel> GetZhaoLuJiHuaDetailModel(int id, int year)
        {
            ObservableCollection<ZhaoLuJiHuaDetailModel> rights = new ObservableCollection<ZhaoLuJiHuaDetailModel>();
            string sql = "";
            //if (id != 0)
            //{
            sql = string.Format(@"SELECT distinct zd.[ID]
      ,[ZhaoLuJiHuaID]
      ,[HDXZBZS_QJJG]
      ,[HDXZBZS_JD]
      ,[HDXZBZS_XZ]
      ,[HDXZBZS_ZF]
      ,[SYRS_QJJG]
      ,[SYRS_JD]
      ,[SYRS_XZ]
      ,[SYRS_ZF]
      ,[JYRS_QJJG]
      ,[JYRS_JD]
      ,[JYRS_XZ]
      ,[JYRS_ZF]
      ,[QBRS_QJJG]
      ,[QBRS_JD]
      ,[QBRS_XZ]
      ,[QBRS_ZF]
      ,[ZS_QJJG]
      ,[ZS_JD]
      ,[ZS_XZ]
      ,[ZS_ZF]
      ,[YXCGB]
      ,[YXJWHGB]
      ,[DXSCG]
      ,[SZ], LiangNian, BX,HDXZBZS_HJ,SYRS_HJ,JYRS_HJ,QBRS_HJ,ZS_HJ
  FROM  ZhaoLuJiHua z 
  inner join ZhaoLuJiHuaDetail zd on zd.ZhaoLuJiHuaID = z.ID 
  left join 
  (select * from ZhaoLuZhiWei where IsLocked = 0 and Status = 4) zw  on zw.Year = z.id
  where z.IsLocked = 0 and 1=1");
            sql += " and ZhaoLuJiHuaID = " + id;
            //}
            //            else
            //            {
            //                sql = string.Format(@"SELECT distinct zd.[ID]
            //      ,[ZhaoLuJiHuaID]
            //      ,[HDXZBZS_QJJG]
            //      ,[HDXZBZS_JD]
            //      ,[HDXZBZS_XZ]
            //      ,[HDXZBZS_ZF]
            //      ,[SYRS_QJJG]
            //      ,[SYRS_JD]
            //      ,[SYRS_XZ]
            //      ,[SYRS_ZF]
            //      ,[JYRS_QJJG]
            //      ,[JYRS_JD]
            //      ,[JYRS_XZ]
            //      ,[JYRS_ZF]
            //      ,[QBRS_QJJG]
            //      ,[QBRS_JD]
            //      ,[QBRS_XZ]
            //      ,[QBRS_ZF]
            //      ,[ZS_QJJG]
            //      ,[ZS_JD]
            //      ,[ZS_XZ]
            //      ,[ZS_ZF]
            //      ,[YXCGB]
            //      ,[YXJWHGB]
            //      ,[DXSCG]
            //      ,[SZ],case when a.Cou is null then 0 else a.Cou end LiangNian,case when b.Cou is null then 0 else b.Cou end BX
            //,HDXZBZS_HJ,SYRS_HJ,JYRS_HJ,QBRS_HJ,ZS_HJ
            //  FROM ZhaoLuZhiWei zw 
            //  left join ZhaoLuJiHua z on zw.Year = z.Year
            //  left join ZhaoLuJiHuaDetail zd on zd.ZhaoLuJiHuaID = z.ID
            //  left join
            //  (select sum(PeopleCount) Cou,YEAR from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
            //  and ZKDX = '具有两年以上基层工作经历的人员' group by YEAR) a on a.Year = z.Year
            //  left join
            //  (select sum(PeopleCount) Cou,YEAR from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
            //  and ZKDX = '不限'  group by YEAR) b on b.Year = z.Year where zw.IsLocked = 0 and zw.Status = 4  ", year);
            //            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaDetailModel.FillEntity(dr));
            }
            return rights;
        }
        public ObservableCollection<ZhaoLuJiHuaDetail1Model> GetZhaoLuJiHuaDetail1Model(int id, int year)
        {
            ObservableCollection<ZhaoLuJiHuaDetail1Model> rights = new ObservableCollection<ZhaoLuJiHuaDetail1Model>();
            string sql = "";
            if (id != 0)
            {
                sql = string.Format(@"  select ROW_NUMBER() OVER(ORDER BY zd.id ) Sec, zd.*,zw.Duty,o.Name OrgName , (o.Name + zw.Duty) as OrgName_Duty,zw.ID ZhaoLuZhiWeiID  
from ZhaoLuJiHua z
inner join ZhaoLuJiHuaDetail1 zd on z.ID = zd.zhaolujihuaid
inner join zhaoluzhiwei zw on zw.id = zd.zhaoluzhiweiid
inner join OrgForHR o on o.ID = zw.OrgID where 1=1  ");
                sql += " and ZhaoLuJiHuaID = " + id;
            }
            else
            {
                sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY zd.id ) Sec, zd.ID,zd.ZhaoLuJiHuaID,zw.ID ZhaoLuZhiWeiID, zw.Duty,o.Name OrgName, (o.Name + zw.Duty) as OrgName_Duty,a.Cou LiangNian,b.Cou BX,null YXCGB,null YXJWHGB,null DXSCG,null SanZhi,c.Cou ZF,
d.Cou ZHGL,e.Cou XXGL,f.Cou CWGL,g.Cou JJGL,h.Cou CSJSGL,i.Cou NCJSGL,
j.Cou BSYJS,k.Cou SSJYS,l.Cou SSYJS,m.Cou BKJYS,n.Cou BK,p.Cou DZJYS,q.Cou DZ,
r.Cou BS,s.Cou SS,t.Cou XS,u.Cou WYQ from ZhaoLuZhiWei zw
 left join ZhaoLuJiHuaDetail1 zd on zw.ID = zd.ZhaoLuZhiWeiID
 left join ZhaoLuJiHua z on z.ID = zd.ZhaoLuJiHuaID
 left join OrgForHR o on o.ID = zw.OrgID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZKDX = '具有两年以上基层工作经历的人员' group by YEAR,ID) a on a.Year = zw.Year and zw.ID = a.ID
 left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZKDX = '不限'  group by YEAR,ID) b on b.Year = zw.Year and zw.ID = b.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '政法'  group by YEAR,ID) c on c.Year = zw.Year and zw.ID = c.ID
    left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '综合管理'  group by YEAR,ID) d on d.Year = zw.Year and zw.ID = d.ID
    left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '信息管理'  group by YEAR,ID) e on e.Year = zw.Year and zw.ID = e.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '财务管理'  group by YEAR,ID) f on f.Year = zw.Year and zw.ID = f.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '经济管理'  group by YEAR,ID) g on g.Year = zw.Year and zw.ID = g.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '城市建设管理'  group by YEAR,ID) h on h.Year = zw.Year and zw.ID = h.ID
   left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and ZYKSKM = '农村建设管理'  group by YEAR,ID) i on i.Year = zw.Year and zw.ID = i.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '博士研究生'  group by YEAR,ID) j on j.Year = zw.Year and zw.ID = j.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '硕士及以上'  group by YEAR,ID) k on k.Year = zw.Year and zw.ID = k.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '硕士研究生'  group by YEAR,ID) l on l.Year = zw.Year and zw.ID = l.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '本科及以上'  group by YEAR,ID) m on m.Year = zw.Year and zw.ID = m.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '本科'  group by YEAR,ID) n on n.Year = zw.Year and zw.ID = n.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '大专及以上'  group by YEAR,ID) p on p.Year = zw.Year and zw.ID = p.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueLiYaoQiu = '大专'  group by YEAR,ID) q on q.Year = zw.Year and zw.ID = q.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '博士'  group by YEAR,ID) r on r.Year = zw.Year and zw.ID = r.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '硕士'  group by YEAR,ID) s on s.Year = zw.Year and zw.ID = s.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '学士'  group by YEAR,ID) t on t.Year = zw.Year and zw.ID = t.ID
  left join
  (select sum(PeopleCount) Cou,YEAR,ID from ZhaoLuZhiWei where IsLocked = 0 and Status = 4 and YEAR = {0}
  and XueWeiYaoQiu = '无要求'  group by YEAR,ID) u on u.Year = zw.Year and zw.ID = u.ID
  where zw.IsLocked = 0 and zw.Status = 4", year);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuJiHuaDetail1Model.FillEntity(dr));
            }
            return rights;
        }

        public int InsertZhaoLuJiHuaTitle(ZhaoLuJiHuaModel ZhaoLuJiHua)
        {
            string sql = "";
            if (ZhaoLuJiHua != null)
            {
                if (ZhaoLuJiHua.ID == 0)
                    sql = string.Format(@"INSERT INTO ZhaoLuJiHua
           ([Year]
           ,[Name]
           ,[IsLocked])
     VALUES
           ({0}
           ,'{1}'
           ,'{2}')  ;select @@IDENTITY;", ZhaoLuJiHua.Year, ZhaoLuJiHua.Name, ZhaoLuJiHua.IsLocked);
                else
                    sql = string.Format("update ZhaoLuJiHua set Name = '{0}' where id = {1};select 0 from ZhaoLuJiHua", ZhaoLuJiHua.Name,
                                        ZhaoLuJiHua.ID);
            }

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            else return 0;
        }

        public bool DeleteZhaoLuJiHua(int id)
        {
            string sql = string.Format(@"delete from ZhaoLuJiHua where id = {0};delete from ZhaoLuJiHuaDetail where ZhaoLuJiHuaID = {0}
                                        ;delete from ZhaoLuJiHuaDetail1 where ZhaoLuJiHuaID = {0}", id);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool InsertZhaoLuJiHua(int ZhaoLuJiHuaID, ZhaoLuJiHuaDetailModel ZhaoLuJiHuaDetail, ObservableCollection<ZhaoLuJiHuaDetail1Model> ZhaoLuJiHuaDetail1)
        {
            ZhaoLuJiHuaDetail.ZhaoLuJiHuaID = ZhaoLuJiHuaID;
            string sql1 = string.Format(@"delete from ZhaoLuJiHuaDetail where ID = {32};INSERT INTO ZhaoLuJiHuaDetail
           ([ZhaoLuJiHuaID]
           ,[HDXZBZS_QJJG]
           ,[HDXZBZS_JD]
           ,[HDXZBZS_XZ]
           ,[HDXZBZS_ZF]
           ,[SYRS_QJJG]
           ,[SYRS_JD]
           ,[SYRS_XZ]
           ,[SYRS_ZF]
           ,[JYRS_QJJG]
           ,[JYRS_JD]
           ,[JYRS_XZ]
           ,[JYRS_ZF]
           ,[QBRS_QJJG]
           ,[QBRS_JD]
           ,[QBRS_XZ]
           ,[QBRS_ZF]
           ,[ZS_QJJG]
           ,[ZS_JD]
           ,[ZS_XZ]
           ,[ZS_ZF]
           ,[LiangNian]
           ,[YXCGB]
           ,[YXJWHGB]
           ,[DXSCG]
           ,[SZ]
           ,[BX],HDXZBZS_HJ,SYRS_HJ,JYRS_HJ,QBRS_HJ,ZS_HJ)
     VALUES
           ({0}
           ,{1}
           ,{2}
           ,{3}
           ,{4}
           ,{5}
           ,{6}
           ,{7}
           ,{8}
           ,{9}
           ,{10}
           ,{11}
           ,{12}
           ,{13}
           ,{14}
           ,{15}
           ,{16}
           ,{17}
           ,{18}
           ,{19}
           ,{20}
           ,{21}
           ,{22}
           ,{23}
           ,{24}
           ,{25}
           ,{26},{27}
           ,{28}
           ,{29}
           ,{30}
           ,{31})", ZhaoLuJiHuaDetail.ZhaoLuJiHuaID, ZhaoLuJiHuaDetail.HDXZBZS_QJJG, ZhaoLuJiHuaDetail.HDXZBZS_JD, ZhaoLuJiHuaDetail.HDXZBZS_XZ, ZhaoLuJiHuaDetail.HDXZBZS_ZF, ZhaoLuJiHuaDetail.SYRS_QJJG,
          ZhaoLuJiHuaDetail.SYRS_JD, ZhaoLuJiHuaDetail.SYRS_XZ, ZhaoLuJiHuaDetail.SYRS_ZF, ZhaoLuJiHuaDetail.JYRS_QJJG, ZhaoLuJiHuaDetail.JYRS_JD, ZhaoLuJiHuaDetail.JYRS_XZ,
          ZhaoLuJiHuaDetail.JYRS_ZF, ZhaoLuJiHuaDetail.QBRS_QJJG, ZhaoLuJiHuaDetail.QBRS_JD, ZhaoLuJiHuaDetail.QBRS_XZ, ZhaoLuJiHuaDetail.QBRS_ZF, ZhaoLuJiHuaDetail.ZS_QJJG,
          ZhaoLuJiHuaDetail.ZS_JD, ZhaoLuJiHuaDetail.ZS_XZ, ZhaoLuJiHuaDetail.ZS_ZF, ZhaoLuJiHuaDetail.LiangNian, ZhaoLuJiHuaDetail.YXCGB, ZhaoLuJiHuaDetail.YXJWHGB,
          ZhaoLuJiHuaDetail.DXSCG, ZhaoLuJiHuaDetail.SZ, ZhaoLuJiHuaDetail.BX,
          ZhaoLuJiHuaDetail.HDXZBZS_HJ, ZhaoLuJiHuaDetail.SYRS_HJ, ZhaoLuJiHuaDetail.JYRS_HJ, ZhaoLuJiHuaDetail.QBRS_HJ, ZhaoLuJiHuaDetail.ZS_HJ, ZhaoLuJiHuaDetail.ID);
            if (!string.IsNullOrEmpty(sql1))
                AccessHelper.GetInstance().ExecuteNonQuery(sql1);
            if (ZhaoLuJiHuaDetail1 != null)
            {
                foreach (ZhaoLuJiHuaDetail1Model i in ZhaoLuJiHuaDetail1)
                {
                    i.ZhaoLuJiHuaID = ZhaoLuJiHuaID;
                    string sql2 = string.Format(@"delete from ZhaoLuJiHuaDetail1 where ID = {26};INSERT INTO ZhaoLuJiHuaDetail1
           ([ZhaoLuJiHuaID]
           ,[ZhaoLuZhiWeiID]
           ,[LiangNian]
           ,[YXCGB]
           ,[YXJWHGB]
           ,[DXSCG]
           ,[SanZhi]
           ,[BX]
           ,[ZF]
           ,[ZHGL]
           ,[XXGL]
           ,[CWGL]
           ,[JJGL]
           ,[CSJSGL]
           ,[NCJSGL]
           ,[BSYJS]
           ,[SSJYS]
           ,[SSYJS]
           ,[BKJYS]
           ,[BK]
           ,[DZJYS]
           ,[DZ]
           ,[BS]
           ,[SS]
           ,[XS]
           ,[WYQ])
     VALUES
           ({0}
           ,{1}
           ,{2}
           ,{3}
           ,{4}
           ,{5}
           ,{6}
           ,{7}
           ,{8}
           ,{9}
           ,{10}
           ,{11}
           ,{12}
           ,{13}
           ,{14}
           ,{15}
           ,{16}
           ,{17}
           ,{18}
           ,{19}
           ,{20}
           ,{21}
           ,{22}
           ,{23}
           ,{24}
           ,{25})", i.ZhaoLuJiHuaID, i.ZhaoLuZhiWeiID, i.LiangNian, i.YXCGB, i.YXJWHGB, i.DXSCG, i.SanZhi, i.BX, i.ZF, i.ZHGL, i.XXGL, i.CWGL, i.JJGL, i.CSJSGL,
             i.NCJSGL, i.BSYJS, i.SSJYS, i.SSYJS, i.BKJYS, i.BK, i.DZJYS, i.DZ, i.BS, i.SS, i.XS, i.WYQ, i.ID);
                    if (!string.IsNullOrEmpty(sql2))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql2);
                }

            }


            return true;
        }
        #endregion

        #region 导出
        private byte[] _content = null;
        public int _id;
        public FileTransferStream GetExcelFile(ZhaoLuJiHuaDetailModel model, ObservableCollection<ZhaoLuJiHuaDetail1Model> models, ObservableCollection<ZhaoLu_DaoRuKaoShengModel> zlmodels, string username, Dictionary<string, bool> dic)
        {
            FileTransferStream ftfs = new FileTransferStream();
            if (_content != null)
            {
            }
            else
            {
                HSSFWorkbook workbook = null;
                MemoryStream ms = new MemoryStream();
                if (models != null)
                {
                    ms = new MemoryStream(ResourceFile.录用计划表);
                    workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                    ms.Dispose();
                    GetInfo(model, models, workbook);
                }
                if (zlmodels != null)
                {
                    ms = new MemoryStream(ResourceFile.zhaokao_mianshi);
                    workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                    ms.Dispose();
                    ZhaoLuGetInfo(zlmodels, workbook, dic);
                }

                string strFileName = AppDomain.CurrentDomain.BaseDirectory + username + ".xls";
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);

                }
                using (FileStream file = File.Open(strFileName, FileMode.Open))
                {
                    BinaryReader r = new BinaryReader(file);

                    r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

                    _content = r.ReadBytes((int)r.BaseStream.Length);
                }
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }


                //{
                //    _content = new byte[file.Length];
                //    file.Write(_content, 0, _content.Length);
                //}

                ftfs.IsFinished = true;
                ftfs.FileContent = _content;

            }
            return ftfs;
        }
        private DataSet GetInfo(ZhaoLuJiHuaDetailModel model, ObservableCollection<ZhaoLuJiHuaDetail1Model> models, NPOI.HSSF.UserModel.HSSFWorkbook workbook)
        {
            HSSFSheet sheet1 = workbook.GetSheetAt(0);
            #region 上面的

            SetCellValue(sheet1, 7, 1, model.HDXZBZS_HJ.ToString());
            SetCellValue(sheet1, 7, 2, model.HDXZBZS_QJJG.ToString());
            SetCellValue(sheet1, 7, 3, model.HDXZBZS_JD.ToString());
            SetCellValue(sheet1, 7, 4, model.HDXZBZS_XZ.ToString());
            SetCellValue(sheet1, 7, 5, model.HDXZBZS_ZF.ToString());
            SetCellValue(sheet1, 7, 10, model.SYRS_HJ.ToString());
            SetCellValue(sheet1, 7, 11, model.SYRS_QJJG.ToString());
            SetCellValue(sheet1, 7, 12, model.SYRS_JD.ToString());
            SetCellValue(sheet1, 7, 13, model.SYRS_XZ.ToString());

            SetCellValue(sheet1, 7, 14, model.SYRS_ZF.ToString());
            SetCellValue(sheet1, 7, 19, model.JYRS_HJ.ToString());
            SetCellValue(sheet1, 7, 20, model.JYRS_QJJG.ToString());
            SetCellValue(sheet1, 7, 21, model.JYRS_JD.ToString());
            SetCellValue(sheet1, 7, 22, model.JYRS_XZ.ToString());
            SetCellValue(sheet1, 7, 23, model.JYRS_ZF.ToString());
            SetCellValue(sheet1, 7, 28, model.QBRS_HJ.ToString());

            SetCellValue(sheet1, 7, 29, model.QBRS_QJJG.ToString());
            SetCellValue(sheet1, 7, 30, model.QBRS_JD.ToString());
            SetCellValue(sheet1, 7, 31, model.QBRS_XZ.ToString());
            SetCellValue(sheet1, 7, 32, model.QBRS_ZF.ToString());
            SetCellValue(sheet1, 7, 37, model.ZS_HJ.ToString());
            SetCellValue(sheet1, 7, 38, model.ZS_QJJG.ToString());
            SetCellValue(sheet1, 7, 39, model.ZS_JD.ToString());
            SetCellValue(sheet1, 7, 40, model.ZS_XZ.ToString());
            SetCellValue(sheet1, 7, 41, model.ZS_ZF.ToString());
            SetCellValue(sheet1, 7, 46, model.LiangNian.ToString());
            SetCellValue(sheet1, 7, 48, model.YXCGB.ToString());

            SetCellValue(sheet1, 7, 49, model.YXJWHGB.ToString());
            SetCellValue(sheet1, 7, 50, model.DXSCG.ToString());
            SetCellValue(sheet1, 7, 51, model.SZ.ToString());
            SetCellValue(sheet1, 7, 52, model.BX.ToString());
            #endregion
            #region 下面的
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            for (int i = 0; i < models.Count; i++)
            {
                var row = sheet1.CreateRow(i + 9);
                row.Height = 20 * 20;
                for (int ii = 1; ii < 53; ii++)
                {
                    HSSFCell cell = row.CreateCell(ii);
                    cell.CellStyle = style;
                }
                sheet1.AddMergedRegion(new Region(i + 9, 2, i + 9, 10));
                sheet1.AddMergedRegion(new Region(i + 9, 11, i + 9, 12));
                sheet1.AddMergedRegion(new Region(i + 9, 13, i + 9, 14));
                sheet1.AddMergedRegion(new Region(i + 9, 15, i + 9, 16));
                sheet1.AddMergedRegion(new Region(i + 9, 17, i + 9, 18));
                sheet1.AddMergedRegion(new Region(i + 9, 20, i + 9, 21));
                sheet1.AddMergedRegion(new Region(i + 9, 22, i + 9, 23));
                sheet1.AddMergedRegion(new Region(i + 9, 24, i + 9, 25));
                sheet1.AddMergedRegion(new Region(i + 9, 26, i + 9, 27));
                sheet1.AddMergedRegion(new Region(i + 9, 28, i + 9, 29));
                sheet1.AddMergedRegion(new Region(i + 9, 30, i + 9, 31));
                sheet1.AddMergedRegion(new Region(i + 9, 32, i + 9, 33));
                sheet1.AddMergedRegion(new Region(i + 9, 34, i + 9, 36));
                sheet1.AddMergedRegion(new Region(i + 9, 38, i + 9, 39));
                sheet1.AddMergedRegion(new Region(i + 9, 40, i + 9, 41));
                sheet1.AddMergedRegion(new Region(i + 9, 42, i + 9, 43));

                sheet1.AddMergedRegion(new Region(i + 9, 44, i + 9, 45));
                sheet1.AddMergedRegion(new Region(i + 9, 50, i + 9, 52));
                SetCellValue(sheet1, i + 10, 1, models[i].OrgName_Duty.ToString());
                SetCellValue(sheet1, i + 10, 2, models[i].LiangNian.ToString());
                SetCellValue(sheet1, i + 10, 11, models[i].YXCGB.ToString());
                SetCellValue(sheet1, i + 10, 13, models[i].YXJWHGB.ToString());
                SetCellValue(sheet1, i + 10, 15, models[i].DXSCG.ToString());
                SetCellValue(sheet1, i + 10, 17, models[i].SanZhi.ToString());
                SetCellValue(sheet1, i + 10, 19, models[i].BX.ToString());
                SetCellValue(sheet1, i + 10, 20, models[i].ZF.ToString());
                SetCellValue(sheet1, i + 10, 22, models[i].ZHGL.ToString());

                SetCellValue(sheet1, i + 10, 24, models[i].XXGL.ToString());
                SetCellValue(sheet1, i + 10, 26, models[i].CWGL.ToString());
                SetCellValue(sheet1, i + 10, 28, models[i].JJGL.ToString());
                SetCellValue(sheet1, i + 10, 30, models[i].CSJSGL.ToString());
                SetCellValue(sheet1, i + 10, 32, models[i].NCJSGL.ToString());
                SetCellValue(sheet1, i + 10, 34, models[i].BSYJS.ToString());
                SetCellValue(sheet1, i + 10, 37, models[i].SSJYS.ToString());

                SetCellValue(sheet1, i + 10, 38, models[i].SSYJS.ToString());
                SetCellValue(sheet1, i + 10, 40, models[i].BKJYS.ToString());
                SetCellValue(sheet1, i + 10, 42, models[i].BK.ToString());
                SetCellValue(sheet1, i + 10, 44, models[i].DZJYS.ToString());
                SetCellValue(sheet1, i + 10, 46, models[i].DZ.ToString());
                SetCellValue(sheet1, i + 10, 47, models[i].BS.ToString());
                SetCellValue(sheet1, i + 10, 48, models[i].SS.ToString());
                SetCellValue(sheet1, i + 10, 49, models[i].XS.ToString());
                SetCellValue(sheet1, i + 10, 50, models[i].WYQ.ToString());
            }


            #endregion
            return null;
        }
        private DataSet ZhaoLuGetInfo(ObservableCollection<ZhaoLu_DaoRuKaoShengModel> models, HSSFWorkbook workbook, Dictionary<string, bool> dic)
        {
            HSSFSheet sheet1 = workbook.GetSheetAt(1);
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            #region 基本信息

            for (int i = 0; i < models.Count; i++)
            {
                var row = sheet1.CreateRow(i + 3);
                row.Height = 20 * 20;
                for (int ii = 0; ii < 40; ii++)
                {
                    HSSFCell cell = row.CreateCell(ii);
                    cell.CellStyle = style;
                }
                SetCellValue(sheet1, i + 4, 0, models[i].Sec.ToString());
                SetCellValue(sheet1, i + 4, 1, models[i].RiQi != null ? Convert.ToDateTime(models[i].RiQi).ToString("yyyy-MM-dd") : null);
                SetCellValue(sheet1, i + 4, 2, models[i].Zu);
                SetCellValue(sheet1, i + 4, 3, models[i].Shi);
                SetCellValue(sheet1, i + 4, 4, models[i].ZLDW);
                SetCellValue(sheet1, i + 4, 5, models[i].YRBM);
                SetCellValue(sheet1, i + 4, 6, models[i].BBZW);
                SetCellValue(sheet1, i + 4, 7, models[i].ZCBH.ToString());
                SetCellValue(sheet1, i + 4, 8, models[i].XingMing);
                SetCellValue(sheet1, i + 4, 9, models[i].SFZH);

                SetCellValue(sheet1, i + 4, 10, models[i].ZWID);
                SetCellValue(sheet1, i + 4, 11, models[i].ZKKM);

                SetCellValue(sheet1, i + 4, 12, models[i].KaoShengLeiBie);
                SetCellValue(sheet1, i + 4, 13, models[i].ShiFouTiaoJi);
                SetCellValue(sheet1, i + 4, 14, models[i].ZongFen);
                SetCellValue(sheet1, i + 4, 15, models[i].XB);
                SetCellValue(sheet1, i + 4, 16, models[i].CSRQ != null ? Convert.ToDateTime(models[i].CSRQ).ToString("yyyy-MM-dd") : null);
                SetCellValue(sheet1, i + 4, 17, models[i].JKCD);

                SetCellValue(sheet1, i + 4, 18, models[i].MZ);
                SetCellValue(sheet1, i + 4, 19, models[i].HYZK);
                SetCellValue(sheet1, i + 4, 20, models[i].KSSF);
                SetCellValue(sheet1, i + 4, 21, models[i].XL);
                SetCellValue(sheet1, i + 4, 22, models[i].ZZMM);
                SetCellValue(sheet1, i + 4, 23, models[i].XW);
                SetCellValue(sheet1, i + 4, 24, models[i].BKBYYX);
                SetCellValue(sheet1, i + 4, 25, models[i].BKZY);
                SetCellValue(sheet1, i + 4, 26, models[i].SSYX);
                SetCellValue(sheet1, i + 4, 27, models[i].SSZY);
                SetCellValue(sheet1, i + 4, 28, models[i].BSYX);
                SetCellValue(sheet1, i + 4, 29, models[i].BSZY);
                SetCellValue(sheet1, i + 4, 30, models[i].GRJL);

                SetCellValue(sheet1, i + 4, 31, models[i].TXDZ);
                SetCellValue(sheet1, i + 4, 32, models[i].LXFS);
                SetCellValue(sheet1, i + 4, 33, models[i].SJHM);
                SetCellValue(sheet1, i + 4, 34, models[i].GZDW);
                SetCellValue(sheet1, i + 4, 35, models[i].GZNX);
                SetCellValue(sheet1, i + 4, 36, models[i].SY);
                SetCellValue(sheet1, i + 4, 37, models[i].HJ);
                SetCellValue(sheet1, i + 4, 38, models[i].YB);
                SetCellValue(sheet1, i + 4, 39, models[i].BaoMingBeiZhu);
            }
            #endregion
            //hide column C
            if (dic["面试日期"] == false) sheet1.SetColumnHidden(1, true);
            if (dic["场次"] == false) sheet1.SetColumnHidden(2, true);
            if (dic["面试时间"] == false) sheet1.SetColumnHidden(3, true);
            if (dic["招录单位"] == false) sheet1.SetColumnHidden(4, true);
            if (dic["用人部门"] == false) sheet1.SetColumnHidden(5, true);
            if (dic["报考职位"] == false) sheet1.SetColumnHidden(6, true);
            if (dic["注册编号"] == false) sheet1.SetColumnHidden(7, true);
            if (dic["姓名"] == false) sheet1.SetColumnHidden(8, true);
            if (dic["身份证号"] == false) sheet1.SetColumnHidden(9, true);
            if (dic["职位ID"] == false) sheet1.SetColumnHidden(10, true);

            if (dic["招考科目"] == false) sheet1.SetColumnHidden(11, true);
            if (dic["考试类别"] == false) sheet1.SetColumnHidden(12, true);
            if (dic["是否调剂"] == false) sheet1.SetColumnHidden(13, true);
            if (dic["总分"] == false) sheet1.SetColumnHidden(14, true);
            if (dic["性别"] == false) sheet1.SetColumnHidden(15, true);
            if (dic["出生日期"] == false) sheet1.SetColumnHidden(16, true);
            if (dic["健康程度"] == false) sheet1.SetColumnHidden(17, true);
            if (dic["民族"] == false) sheet1.SetColumnHidden(18, true);
            if (dic["婚姻状况"] == false) sheet1.SetColumnHidden(19, true);
            if (dic["考生身份"] == false) sheet1.SetColumnHidden(20, true);
            if (dic["学历"] == false) sheet1.SetColumnHidden(21, true);

            if (dic["政治面貌"] == false) sheet1.SetColumnHidden(22, true);
            if (dic["学位"] == false) sheet1.SetColumnHidden(23, true);
            if (dic["本科毕业院校"] == false) sheet1.SetColumnHidden(24, true);
            if (dic["本科专业"] == false) sheet1.SetColumnHidden(25, true);
            if (dic["硕士毕业院校"] == false) sheet1.SetColumnHidden(26, true);
            if (dic["硕士专业"] == false) sheet1.SetColumnHidden(27, true);
            if (dic["博士毕业院校"] == false) sheet1.SetColumnHidden(28, true);
            if (dic["博士专业"] == false) sheet1.SetColumnHidden(29, true);
            if (dic["个人简历"] == false) sheet1.SetColumnHidden(30, true);

            if (dic["通讯地址"] == false) sheet1.SetColumnHidden(31, true);
            if (dic["联系方式"] == false) sheet1.SetColumnHidden(32, true);
            if (dic["手机号码"] == false) sheet1.SetColumnHidden(33, true);
            if (dic["工作单位"] == false) sheet1.SetColumnHidden(34, true);
            if (dic["工作年限"] == false) sheet1.SetColumnHidden(35, true);
            if (dic["生源"] == false) sheet1.SetColumnHidden(36, true);
            if (dic["户籍"] == false) sheet1.SetColumnHidden(37, true);
            if (dic["邮编"] == false) sheet1.SetColumnHidden(38, true);
            if (dic["报名备注信息"] == false) sheet1.SetColumnHidden(39, true);
            return null;
        }
        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFCell cell, object value)
        {
            if (value != null)
            {
                cell.SetCellValue(value.ToString());
            }
            return true;
        }
        private bool SetCellValue(NPOI.HSSF.UserModel.HSSFSheet sheet, int row, int column, string dr)
        {
            HSSFRow hrow = sheet.GetRow(row - 1);
            if (hrow != null)
            {
                HSSFCell cell = hrow.GetCell(column);
                if (cell != null)
                {
                    SetCellValue(cell, dr);
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 导入数据前判断是否存在错误数据
        /// </summary>
        /// <param name="InEmpBasicModels"></param>
        /// <returns></returns>
        public bool CanDaoRuAll(int year, int month)
        {
            string sql = string.Format(@"select count(ID) Cou  from ZhaoLu_DaoRuKaoSheng where year = {0} ", year);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                if (Convert.ToInt32(ds.Tables[0].Rows[0][0]) > 0) return false;
                else return true;
            }
            else return true;
        }

        public ObservableCollection<ZhaoLu_DaoRuKaoShengModel> GetZhaoLu_DaoRuKaoShengModel(int year, bool isfangqi, int month, int id, string nitongyi,
            string tijianhege, string zhengshenhege, DateTime? dt, string zu, string shangxiawu, string orgids)
        {
            ObservableCollection<ZhaoLu_DaoRuKaoShengModel> rights = new ObservableCollection<ZhaoLu_DaoRuKaoShengModel>();
            
          
            try
            {
                string sql = "";
                if (id == 0)
                {
                    //if (isfangqi == false)
                    sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore ) Sec,*,'false' as IsSelected,zz.zu Zu,zz.riqi RiQi,z.OrgID from ZhaoLu_DaoRuKaoSheng
zdk 
left join ZhaoLuZhiWei z on zdk.zwid = z.ZhiWeiDaiMa
left join ZL_ZhiWeiShiJianAnPai zz on z.id = zz.PublicOfficialSelectionID
inner join orgforhr o on o.id = z.orgid where zdk.year = {0} ", year);
                    //                else
                    //                    sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore) Sec,*,'false' as IsSelected,0 as MingCi from ZhaoLu_DaoRuKaoSheng
                    //zdk 
                    //left join ZhaoLuZhiWei z on zdk.zwid = z.ZhiWeiDaiMa
                    //left join ZL_ZhiWeiShiJianAnPai zz on z.id = zz.PublicOfficialSelectionID where zdk.year = {0} and IsFangQi = 1 ", year);
                    if (!string.IsNullOrEmpty(nitongyi))
                    {
                        if (nitongyi == "拟同意")
                            sql += " and istongyi = '" + nitongyi + "'";
                        else sql += " and istongyi != '" + nitongyi + "'";
                    }
                    if (!string.IsNullOrEmpty(tijianhege)) sql += " and TiJianResult = '" + tijianhege + "'";
                    if (!string.IsNullOrEmpty(zhengshenhege)) sql += " and ZhengShenResult = '" + zhengshenhege + "'";
                }
                else sql = string.Format(@"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore ) Sec,*,'false' as IsSelected,z.OrgID from ZhaoLu_DaoRuKaoSheng
zdk 
left join ZhaoLuZhiWei z on zdk.zwid = z.ZhiWeiDaiMa
left join ZL_ZhiWeiShiJianAnPai zz on z.id = zz.PublicOfficialSelectionID 
inner join orgforhr o on o.id = z.orgid where zdk.id = {0}", id);
                if (dt != null) sql += " and convert(datetime,zz.riqi) = '" + dt + "'";
                if (!string.IsNullOrEmpty(zu) && zu != "全部") sql += " and zz.zu = '" + zu + "'";
                if (shangxiawu != "无")
                {
                    if (shangxiawu == "上午")
                    {
                        sql += " and datepart(hh,zz.startshi) <= 12";
                    }
                    if (shangxiawu == "下午")
                    {
                        sql += " and datepart(hh,zz.startshi) >= 12";
                    }
                }
                if (orgids != "0" && !string.IsNullOrEmpty(orgids))
                {
                    sql += " and (o.ParentID in (" + orgids + ") or o.ID in (" + orgids + "))";
                }
                sql += " order by YRBM,BBZW,TotalScore desc";
                DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    ZhaoLu_DaoRuKaoShengModel khm = new ZhaoLu_DaoRuKaoShengModel();
                    khm = ZhaoLu_DaoRuKaoShengModel.FillEntity(dr);
                    ObservableCollection<ZhaoLuZhengShen_AttatchmentModel> temp = GetZhaoLuZhengShen_Attatchment(Convert.ToInt32(dr["ID"]));
                    if (temp != null && temp.Count > 0)
                    {
                        foreach (ZhaoLuZhengShen_AttatchmentModel i in temp)
                        {
                            if (!khm.Attachment.Contains(i))
                                khm.Attachment.Add(i);
                        }
                    }
                    rights.Add(khm);
                }
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro(id > 0 ? "获取政审情况详细信息" : "获取招录考生信息列表", ex);
            }

            return rights;
        }
        public bool ShanChongFu(int year, int month)
        {
            string sql = "delete from ZhaoLu_DaoRuKaoSheng where year =" + year;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }
        public bool DaoRuZhaoLuKaoSheng(ZhaoLu_DaoRuKaoShengModel model)
        {
            string sql = "";
            //if(isfugai == "是")
            //{
            //    sql = "delete from ZhaoLu_DaoRuKaoSheng where year =" + models[0].Year ;
            //    if (!string.IsNullOrEmpty(sql))
            //        AccessHelper.GetInstance().ExecuteNonQuery(sql);
            //}
            //foreach(ZhaoLu_DaoRuKaoShengModel model in models)
            //{
            sql = string.Format(@"INSERT INTO ZhaoLu_DaoRuKaoSheng
           ([Year]
           ,[ZCBH]
           ,[XingMing]
           ,[SFZH]
           ,[ZWID]
           ,[ZLDW]
           ,[BBZW]
           ,[ZKKM]
           ,[SL]
           ,[XC]
--      ,[ZF]
           ,[ZongFen]
           ,[XB]
           ,[CSRQ]
           ,[JKCD]
--           ,[SG]
--           ,[TZ]
           ,[MZ]
           ,[HYZK]
--          ,[POXL]
           ,[KSSF]
           ,[XL]
           ,[ZZMM]
           ,[XW]
           ,[BKBYYX]
           ,[BKZY]
--          ,[BKBYSJ]
           ,[SSYX]
           ,[SSZY]
--           ,[SSBYSJ]
           ,[BSYX]
           ,[BSZY]
--           ,[BSBYSJ]
           ,[GRJL]
--           ,[TC]
--           ,[SXZYKC]
--          ,[WY1]
--          ,[WY2]
--           ,[WYDJ1]
--          ,[WYDJ2]
--          ,[JSJSP]
           ,[TXDZ]
           ,[LXFS]
           ,[SJHM]
           ,[GZDW]
           ,[GZNX]
--          ,[BYSJB]
           ,[SY]
           ,[HJ]
           ,[YB]
           ,[AddDay],YRBM,month,isfangqi,KaoShengLeiBie,ShiFouTiaoJi,BaoMingBeiZhu)
     VALUES
           ({0}
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}'
           ,'{20}'
           ,'{21}'
           ,'{22}'
           ,'{23}'
           ,'{24}'
           ,'{25}'
           ,'{26}'
           ,'{27}'
           ,'{28}'
           ,'{29}'
           ,'{30}'
           ,'{31}'
           ,'{32}'
           ,'{33}'
           ,'{34}'
           ,'{35}','{36}',{37},'{38}','{39}','{40}','{41}')", model.Year
       , model.ZCBH
       , model.XingMing
       , model.SFZH
       , model.ZWID
       , model.ZLDW
       , model.BBZW
       , model.ZKKM
       , model.SL
       , model.XC
                //, model.ZF
       , model.ZongFen
       , model.XB
       , model.CSRQ
       , model.JKCD
                //, model.SG
                //, model.TZ
       , model.MZ
       , model.HYZK
                //, model.POXL
       , model.KSSF
       , model.XL
       , model.ZZMM
       , model.XW
       , model.BKBYYX
       , model.BKZY
                //, model.BKBYSJ
       , model.SSYX
       , model.SSZY
                //, model.SSBYSJ
       , model.BSYX
       , model.BSZY
                //, model.BSBYSJ
       , model.GRJL
                //, model.TC
                //, model.SXZYKC
                //, model.WY1
                //, model.WY2
                //, model.WYDJ1
                //, model.WYDJ2
                //, model.JSJSP
       , model.TXDZ
       , model.LXFS
       , model.SJHM
       , model.GZDW
       , model.GZNX
                //, model.BYSJB
       , model.SY
       , model.HJ
       , model.YB
       , model.AddDay, model.YRBM, model.Month, model.IsFangQi, model.KaoShengLeiBie, model.ShiFouTiaoJi, model.BaoMingBeiZhu);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);

            //}
            return true;
        }

        #endregion

        public ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> ZL_PublicOfficialSelectionForAnPai(int year)
        {
            ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel>();
            //            string sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.* from 
            //(select b.* from
            //(select distinct top 100 percent (YRBM + '-' + BBZW) as u  from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and Year = " + year + @") a inner join
            //(select (o.Name + '-' + zzw.Duty) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where IsLocked = 0 and Status = 4 and Year = " + year + @") b
            //on a.u = b.u1) zzw 
            //left join ZL_ZhiWeiShiJianAnPai zw on zzw.ID = zw.PublicOfficialSelectionID
            //where zzw.Year = " + year + " and  zzw.IsLocked = 0 and zzw.Status = 4 order by RiQi,Sec1 ,EndShi  ";
            string sql = @"select * from 
(select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec,rq=case when RiQi is null then 2 else 1 end,CONVERT(datetime,StartShi) ss, zw.*,RenShu as RenShu1 from ZL_ZhiWeiShiJianAnPai zw right join 
(select b.* from
(select distinct top 100 percent (YRBM + '-' + BBZW) as u  from ZhaoLu_DaoRuKaoSheng where Year = " + year + @") a right join
(select (o.Name + '-' + zzw.Duty) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where Status = 4 and Year = " + year + @") b
on a.u = b.u1) zzw 
 on zzw.ID = zw.PublicOfficialSelectionID
where zzw.Year = " + year + " and  zzw.IsLocked = 0 and zzw.Status = 4 ) a order by rq,RiQi,ss, sec1 ";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZL_ZhiWeiShiJianAnPaiModel.FillEntity(dr));
            }
            return rights;
        }

        public ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> ZL_PublicOfficialSelectionForAnPaiSuoDing(int year)
        {
            ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel> rights = new ObservableCollection<ZL_ZhiWeiShiJianAnPaiModel>();
            string sql = "";
            //            sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.RiQi ,zw.Zu,pos.Duty,'' ZhuKaoGuanS,'' ZhuanYeKaoGuan1S,
            //'' ZhuanYeKaoGuan2S,'' DanWeiKaoGuan1S,zw.ID,
            //'' DanWeiKaoGuan2S,'' MianShiJianDuYuan,'' GongZuoRenGuan1,'' GongZuoRenGuan2,'' Shi,'' RenShu,'' DiDian from PublicOfficialSelection pos  left join ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
            //where  pos.IsLocked = 1 and Status = 4 and pos.Year = " + year + " and pos.FaBuType = '" + tiaojian + "'";
            sql = @"select ROW_NUMBER() OVER(ORDER BY zw.ID) Sec, zw.RiQi ,zw.Zu,pos.Duty,k.Name ZhuKaoGuanS,kk.Name ZhuanYeKaoGuan1S,
kkk.Name ZhuanYeKaoGuan2S,kkkk.Name DanWeiKaoGuan1S,zw.ID,
kkkkk.Name DanWeiKaoGuan2S,zw.MianShiJianDuYuan,zw.GongZuoRenGuan1,zw.GongZuoRenGuan2,zw.StartShi,zw.EndShi,
zw.RenShu,zw.DiDian 
from PublicOfficialSelection pos  
left join ZL_ZhiWeiShiJianAnPai zw on pos.ID = zw.PublicOfficialSelectionID
left join KaoGuan k on k.ID = zw.ZhuKaoGuan
left join KaoGuan kk on kk.ID = zw.ZhuanYeKaoGuan1
left join KaoGuan kkk on kkk.ID = zw.ZhuanYeKaoGuan2
left join KaoGuan kkkk on kkkk.ID = zw.DanWeiKaoGuan1
left join KaoGuan kkkkk on kkkkk.ID = zw.DanWeiKaoGuan2
where  Status = 4 and pos.Year = " + year + " and  pos.IsLocked = 1 and zw.isfangqi = 0";
            //if (tiaojian == "竞争性选拔")
            //{
            //    sql += " and IsJZXXB  = 1";
            //}
            //else
            //{
            //    sql += " and IsXDJL  = 1";
            //}
            sql += " order by Sec";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZL_ZhiWeiShiJianAnPaiModel.FillEntity1(dr));
            }
            return rights;
        }

        public Dictionary<int, int> GetZL_MianShiRenShu(int PublicOfficialSelectionID, int year)
        {
            Dictionary<int, int> dic = new Dictionary<int, int>();
            string sql =
                @"select c.cou,b.ID from
(select distinct top 100 percent (YRBM + '-' + BBZW+ '-' + zwid) as u  from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and YEAR = " + year + @") a inner join
(select (o.Name + '-' + zzw.Duty+ '-' + ZhiWeiDaiMa) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where Status = 4 and IsLocked = 0 and Year = " + year + @") b
on a.u = b.u1
inner join
(select COUNT(*) Cou,(YRBM + '-' + BBZW+ '-' + zwid) as u from ZhaoLu_DaoRuKaoSheng where isfangqi = 0 and YEAR = " + year + @" group by (YRBM + '-' + BBZW+ '-' + zwid)) c 
on c.u = a.u ";
            if (PublicOfficialSelectionID != 0)
                sql += " and b.ID =" + PublicOfficialSelectionID;
            //sql += " group by zzw.ID";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                dic.Add(Convert.ToInt32(dr[1]), Convert.ToInt32(dr[0]));
            }
            return dic;
        }

        public ObservableCollection<ZhaoLuZhiWeiModel> ZL_GetPublicOfficialSelectionForDaoChu(int year, string islocked)
        {
            ObservableCollection<ZhaoLuZhiWeiModel> rights = new ObservableCollection<ZhaoLuZhiWeiModel>();
            string sql = "";
            sql =
                @"select distinct zzw.ID,zzw.*,o.Name OrgName from 
((select b.* from
(select distinct top 100 percent (YRBM + '-' + BBZW) as u  from ZhaoLu_DaoRuKaoSheng where Year = " + year + @") a inner join
(select (o.Name + '-' + zzw.Duty) u1,zzw.* from ZhaoLuZhiWei zzw inner join OrgForHR o on zzw.OrgID = o.ID where Status = 4 and IsLocked = '" + islocked + "' and Year = " + year + @") b
on a.u = b.u1) ) zzw inner join OrgForHR o on o.ID = zzw.OrgID
inner join ZhaoLu_DaoRuKaoSheng zld on zld.YRBM = o.Name where zzw.Status = 4 and  zzw.year = " + year + " and zld.Year =" + year;
            sql += " and zzw.IsLocked = '" + islocked + "'";
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuZhiWeiModel.FillEntity(dr));
            }
            return rights;
        }

        public bool ZL_InsertZu(ZL_ZhiWeiShiJianAnPaiModel model, string type)
        {
            switch (model.Zu)
            {
                case "第一组":
                    model.Sec1 = 1;
                    break;
                case "第二组":
                    model.Sec1 = 2;
                    break;
                case "第三组":
                    model.Sec1 = 3;
                    break;
                case "第四组":
                    model.Sec1 = 4;
                    break;
                case "第五组":
                    model.Sec1 = 5;
                    break;
                case "第六组":
                    model.Sec1 = 6;
                    break;
                case "第七组":
                    model.Sec1 = 7;
                    break;
                case "第八组":
                    model.Sec1 = 8;
                    break;
                case "第九组":
                    model.Sec1 = 9;
                    break;
            }
            int iiii = model.PublicOfficialSelectionID;
            string sql = string.Format(@"delete from ZL_ZhiWeiShiJianAnPai where ID = {18} ; INSERT INTO ZL_ZhiWeiShiJianAnPai
           ([PublicOfficialSelectionID]
           ,[Year]
           ,[Zu]
           ,[RiQi]
           ,[ZhuKaoGuan]
           ,[ZhuanYeKaoGuan1]
           ,[ZhuanYeKaoGuan2]
           ,[DanWeiKaoGuan1]
           ,[DanWeiKaoGuan2]
           ,[MianShiJianDuYuan]
           ,[GongZuoRenGuan1]
           ,[GongZuoRenGuan2]
           ,[StartShi]
           ,[RenShu]
           ,[DiDian]
           ,[EndShi],Sec1,type)
     VALUES
           ({0}
           ,{1}
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,'{14}'
           ,'{15}','{16}','{17}')", model.PublicOfficialSelectionID, model.Year,
                    model.Zu,
                    model.RiQi, model.ZhuKaoGuan, model.ZhuanYeKaoGuan1, model.ZhuanYeKaoGuan2, model.DanWeiKaoGuan1
                    , model.DanWeiKaoGuan2, model.MianShiJianDuYuan, model.GongZuoRenGuan1, model.GongZuoRenGuan2, model.StartShi,
                    model.RenShu1, model.DiDian, model.EndShi, model.Sec1, type, model.ID);
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public bool QiQuan(string ID, string huanjie)
        {
          
            try
            {
                if (string.IsNullOrEmpty(ID)) ID = "0";

                    string sql = "update ZhaoLu_DaoRuKaoSheng set IsTongYi = '',ZhengShenResult = '',ZhengShenBeiZhu = '', IsFangQi = 1,FangQiHuanJie = '" + huanjie + "',MianShiBeiZhu = '弃权' where ID in (" + ID + ")";
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro(huanjie+"弃权操作",ex);
            }
            return true;
        }


        public bool UpdateZL_MianShiFenShu(ZhaoLu_DaoRuKaoShengModel model)
        {
            try
            {
           // string sql = "update ZhaoLu_DaoRuKaoSheng set MianShiScore = " + model.MianShiScore.ToString() + ",TotalScore = " + model.TotalScore.ToString() + ",MianShiZheSuan = " + model.MianShiZheSuan.ToString() + ",BiShiZheSuan = " + model.BiShiZheSuan.ToString() + ",MingCi = " + model.MingCi + " where ID =" + model.ID;
                string sql = string.Format(@"update ZhaoLu_DaoRuKaoSheng 
set MianShiScore='{0}',MianShiZheSuan=isnull({0},0)*0.60,
TotalScore=isnull({0},0)*0.60+isnull(BiShiZheSuan,0)
 where id={1}", model.MianShiScore,model.ID);
                if (!string.IsNullOrEmpty(sql)) //面试分数 折算分数 总分进入 根据场次 面试日期 用人部门报名职位排名次
                    if (AccessHelper.GetInstance().ExecuteNonQuery(sql) > 0)
                    {
                        sql = string.Format(@" update ZhaoLu_DaoRuKaoSheng 
 set MingCi=a.mc
 from ZhaoLu_DaoRuKaoSheng zl,
 (select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore desc) mc,zdk.ID,TotalScore from ZhaoLu_DaoRuKaoSheng
zdk 
left join ZhaoLuZhiWei z on zdk.zwid = z.ZhiWeiDaiMa
left join ZL_ZhiWeiShiJianAnPai zz on z.id = zz.PublicOfficialSelectionID
inner join orgforhr o on o.id = z.orgid where zdk.year = 5
and zu='{0}' and RiQi='{1}' and YRBM='{2}' and BBZW='{3}'

  )a 
where zl.ID=a.ID",model.Zu,model.RiQi,model.YRBM,model.BBZW);
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    }
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("保存面试成绩", ex);
            }
            return true;
        }

        public bool UpdateZL_MianShiFenShu_LuRuWanBi(ZhaoLu_DaoRuKaoShengModel model)
        {
            try
            {
            string sql = "update ZhaoLu_DaoRuKaoSheng set IsTongYi = '" + model.IsTongYi.ToString() + "',MianShiBeiZhu = '" + model.MianShiBeiZhu.ToString() + "' where ID =" + model.ID;
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("面试成绩录用完毕", ex);
            }
            return true;
        }

        

        public bool UpdateZL_TiJian(ZhaoLu_DaoRuKaoShengModel model)
        {
            
            try
            {
                string sql = "";
                if (!string.IsNullOrEmpty(model.TiJianResult))
                {
                    sql = string.Format("update ZhaoLu_DaoRuKaoSheng set TiJianResult = '{0}',TiJianBeiZhu = '{2}' where ID ={1}", model.TiJianResult, model.ID, model.TiJianBeiZhu);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    if (model.TiJianBeiZhu == "暂缓")
                    {
                        string sql1 =
                            string.Format(
                                @"insert into ZhaoLu_LuYongGuanLi (ZhaoLu_DaoRuKaoShengID,LuYongLeiXing)
     VALUES
           ({0},'{1}')", model.ID, "暂缓录用");
                        if (!string.IsNullOrEmpty(sql1))
                            AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                    }
                }
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("保存体检结果", ex);
            }
            return true;
        }
        public bool UpdateZL_ZhengShen(ZhaoLu_DaoRuKaoShengModel model)
        {
           
            try
            {
                 string sql = "";
            if (!string.IsNullOrEmpty(model.ZhengShenResult))
            {
                sql = string.Format("update ZhaoLu_DaoRuKaoSheng set ZhengShenResult = '{0}',ZhengShenBeiZhu = '{2}' where ID ={1}", model.ZhengShenResult, model.ID, model.ZhengShenBeiZhu);
                if (!string.IsNullOrEmpty(sql))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                string sql2 = string.Format(@"delete from ZhaoLuZhengShen_Attatchment where mainid = {0}", model.ID);
                if (!string.IsNullOrEmpty(sql2))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql2);
                foreach (ZhaoLuZhengShen_AttatchmentModel i in model.Attachment)
                {
                    string sql1 = string.Format(@"INSERT INTO ZhaoLuZhengShen_Attatchment
           ([MainID],[Key],[filename],[realname],Status,FileSize,Percentage)
     VALUES ({0} ,'{1}','{2}' ,'{3}','{4}','{5}','{6}')"
                        , model.ID, i.Key, i.filename, i.realname, i.Status, i.FileSize, i.Percentage);
                    if (!string.IsNullOrEmpty(sql1))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql1);
                }
                string sql3 =
                        string.Format(
                            @"delete from ZhaoLu_LuYongGuanLi where ZhaoLu_DaoRuKaoShengID = {0};insert into ZhaoLu_LuYongGuanLi ([ZhaoLu_DaoRuKaoShengID])
     VALUES
           ({0})", model.ID);
                if (!string.IsNullOrEmpty(sql3))
                    AccessHelper.GetInstance().ExecuteNonQuery(sql3);
            }
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("维护政审",ex);
            }
            return true;
        }
        /// <summary>
        /// 拟同意或放弃
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public bool NiTongYiorNot_ZL(List<int> PublicOfficialRecordID, string istongyi)
        {
           
            try
            {
                foreach (int id in PublicOfficialRecordID)
                {
                    string sql = string.Format(@"update ZhaoLu_DaoRuKaoSheng set IsTongYi = '{1}',MianShiBeiZhu = '{1}' where ID in ({0})", id, istongyi);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
                }
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("面试成绩" + istongyi != "" ? istongyi : "取消拟同意" + "操作", ex);
            }
            return true;
        }
        public bool ISNiTongYi(string PublicOfficialRecordID, string istongyi)
        {

            try
            {

                string sql = string.Format(@"update ZhaoLu_DaoRuKaoSheng set IsTongYi = '{1}',MianShiBeiZhu = '{1}' where ID in ({0})", PublicOfficialRecordID, istongyi);
                    if (!string.IsNullOrEmpty(sql))
                        AccessHelper.GetInstance().ExecuteNonQuery(sql);
             
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("面试成绩" + istongyi != "" ? istongyi : "取消拟同意" + "操作", ex);
            }
            return true;
        }

        public void InsertEmp(int PublicOfficialRecordID)
        {
            string sql = string.Format(@"insert into dbo.Emp_BasicInfo
(Name,CardNo,OrgID,PositionName,Gender,Birthday,EmpStatus,Health,Native,Marriage,Political,NativePlace)
select XingMing,SFZH,o.id OrgID,YRBM,XB,CSRQ,'在职',JKCD,MZ,HYZK,ZZMM,HJ from dbo.ZhaoLu_DaoRuKaoSheng zl
inner join orgforhr o on zl.ZLDW = o.Name
where zl.id = {0} ;SELECT @@IDENTITY", PublicOfficialRecordID);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            if (!string.IsNullOrEmpty(ds.Tables[0].Rows[0][0].ToString()))
            {
                int EmpID = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                string degreesql = string.Format(@"insert into dbo.Emp_Degree
(empbasicid,degreename)
select {0}, XW from dbo.ZhaoLu_DaoRuKaoSheng where id = {1}", EmpID, PublicOfficialRecordID);
                string dutysql = string.Format(@"insert into dbo.Emp_Duty
(empbasicid,dutyname,okdate)
select {0}, BBZW,getdate() from dbo.ZhaoLu_DaoRuKaoSheng where id = {1}", EmpID, PublicOfficialRecordID);
                string resumesql = string.Format(@"insert into dbo.Emp_Resume
(empbasicid,BeginDate,Unit,Duty)
select {0}, getdate(),ZLDW,BBZW from dbo.ZhaoLu_DaoRuKaoSheng where id = {1}", EmpID, PublicOfficialRecordID);

                if (!string.IsNullOrEmpty(degreesql) && !string.IsNullOrEmpty(dutysql) && !string.IsNullOrEmpty(resumesql))
                {
                    AccessHelper.GetInstance().ExecuteNonQuery(degreesql);
                    AccessHelper.GetInstance().ExecuteNonQuery(dutysql);
                    AccessHelper.GetInstance().ExecuteNonQuery(resumesql);
                }
            }

        }

        public ObservableCollection<ZhaoLu_DaoRuKaoShengModel> GetZhaoLu_LuYongGuanLi(int year)
        {
            string sql = "";
            ObservableCollection<ZhaoLu_DaoRuKaoShengModel> rights = new ObservableCollection<ZhaoLu_DaoRuKaoShengModel>();

            sql = @"select ROW_NUMBER() OVER(ORDER BY YRBM,BBZW,TotalScore ) Sec,z.*,'false' as IsSelected,zl.LuYongLeiXing,zlwz.OrgID from ZhaoLu_LuYongGuanLi zl 
                    inner join dbo.ZhaoLu_DaoRuKaoSheng z 
                    left join ZhaoLuZhiWei zlwz on z.zwid = zlwz.ZhiWeiDaiMa
on zl.ZhaoLu_DaoRuKaoShengID = z.id where z.IsFangQi = 0 and z.year =" + year;

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLu_DaoRuKaoShengModel.FillEntity(dr));
            }
            return rights;
        }

        public bool InsertLuYongLeiXing(ObservableCollection<ZhaoLu_DaoRuKaoShengModel> models, string quedingzanhuan, DateTime? luyongriqi)
        {
           
            try
            {
                DataSet ds = new DataSet();
                foreach (ZhaoLu_DaoRuKaoShengModel id in models)
                {
                    int empid = 0;
                    DateTime? tuixiudate = null;
                    int? jutuixiunianxian = 0;
                    if (id.XB == "男")
                    {
                        tuixiudate =
                            Convert.ToDateTime(Convert.ToDateTime(id.CSRQ).AddYears(60).Year + "-" +
                                               Convert.ToDateTime(id.CSRQ).AddYears(60).Month + "-01");
                        jutuixiunianxian = (Convert.ToDateTime(id.CSRQ).AddYears(60).Year - DateTime.Today.Year);
                    }
                    if (id.XB == "女")
                    {
                        tuixiudate =
                            Convert.ToDateTime(Convert.ToDateTime(id.CSRQ).AddYears(55).Year + "-" +
                                               Convert.ToDateTime(id.CSRQ).AddYears(55).Month + "-01");
                        jutuixiunianxian = (Convert.ToDateTime(id.CSRQ).AddYears(55).Year - DateTime.Today.Year);
                    }
                    string s = string.Format(@"select OrgID from dbo.ZhaoLu_DaoRuKaoSheng zd 
inner join dbo.ZhaoLuZhiWei z on zd.ZWID = z.ZhiWeiDaiMa where zd.id = {0}", id.ID);
                    ds = AccessHelper.GetInstance().ExcuteDataSet(s);
                    int orgid = 0;
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        orgid = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    string sql = string.Format(@"update [ZhaoLu_LuYongGuanLi] set LuYongLeiXing = '{1}',LuYongRiQi = '{2}' where ZhaoLu_DaoRuKaoShengID = {0}", id.ID, quedingzanhuan, luyongriqi);
                    if (quedingzanhuan == "确定录用")
                    {
                        sql += string.Format(@"insert into dbo.Emp_BasicInfo (Name,CardNo,PositionName,Gender,Birthday,Health,Native,Marriage,Political,OrgID,HuoDeFangShi,
NowWorkDate,EmpStatus,TuiXiuDate,JuTuiXiuDate) values
('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9},'{10}','{11}','{12}','{13}','{14}');SELECT @@IDENTITY", id.XingMing, id.SFZH, "", id.XB, id.CSRQ, id.JKCD, id.MZ,
             id.HYZK, id.ZZMM, orgid, "公务员考录", luyongriqi, "在职", tuixiudate, jutuixiunianxian);
                        ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            empid = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                        }
                        if (empid != 0)
                        {
                            string BKBYYXc = "0"; string SSYXc = "0"; string BSYXc = "0";
                            if (!string.IsNullOrEmpty(id.BSYX))
                            {
                                BKBYYXc = "0";
                                SSYXc = "0";
                                BSYXc = "1";
                            }
                            if (!string.IsNullOrEmpty(id.SSYX))
                            {
                                BKBYYXc = "0";
                                SSYXc = "1";
                                BSYXc = "0";
                            }
                            if (!string.IsNullOrEmpty(id.BKBYYX))
                            {
                                BKBYYXc = "1";
                                SSYXc = "0";
                                BSYXc = "0";
                            }
                            string ins = string.Format(@"insert into Emp_Duty (DutyNo,DutyLevel,DutyAttribute,OrganizationName,OKDate,ServiceMode,JobDate,RXRZWSXSJ,JobStatus,EmpbasicID) 
                        values ('{0}','{1}','{2}','{3}','{4}','{5}','{4}','{4}','{6}',{7})", "试用期公务员", "未定职", "非领导职务", id.YRBM, luyongriqi, "新录用公务员", "在任", empid);
                            ins += string.Format(@";insert into Emp_Resume (BeginDate,Unit,Duty,EmpbasicID) values ('{0}','{1}','{2}',{3})", luyongriqi, id.YRBM, "试用期公务员", empid);
                            if (!string.IsNullOrEmpty(id.BKBYYX))
                            {
                                ins += string.Format(@";insert into Emp_Degree (DegreeName,School,Professional,EmpbasicID) values ('{0}','{1}','{2}',{3})", "", id.BKBYYX, id.BKZY, empid);
                                ins += string.Format(@";insert into Emp_Education (EducationName,School,Professional,EmpbasicID,IsTopEducation) values ('{0}','{1}','{2}',{3},'{4}')", "大学", id.BKBYYX, id.BKZY, empid, BKBYYXc);
                            }
                            if (!string.IsNullOrEmpty(id.SSYX))
                            {
                                ins += string.Format(@";insert into Emp_Degree (DegreeName,School,Professional,EmpbasicID) values ('{0}','{1}','{2}',{3})", "", id.SSYX, id.SSZY, empid);
                                ins += string.Format(@";insert into Emp_Education (EducationName,School,Professional,EmpbasicID,IsTopEducation) values ('{0}','{1}','{2}',{3},'{4}')", "硕士研究生", id.SSYX, id.SSZY, empid, SSYXc);
                            }
                            if (!string.IsNullOrEmpty(id.BSYX))
                            {
                                ins += string.Format(@";insert into Emp_Degree (DegreeName,School,Professional,EmpbasicID) values ('{0}','{1}','{2}',{3})", "", id.BSYX, id.BSZY, empid);
                                ins += string.Format(@";insert into Emp_Education (EducationName,School,Professional,EmpbasicID,IsTopEducation) values ('{0}','{1}','{2}',{3},'{4}')", "博士研究生", id.BSYX, id.BSZY, empid, BSYXc);
                            }
                            if (!string.IsNullOrEmpty(ins))
                                AccessHelper.GetInstance().ExecuteNonQuery(ins);
                            if (orgid != 46 && orgid != 47)
                            {
                                string chasql = string.Format(@"INSERT INTO CheGai
           ([EmpID] ,[OldZhiJi] ,[Status] ,[Demo] ,[RelationID],KeyID,[type],CreateDate)
select {0},ed.dutylevel,'未导出','新增人员',{0},
case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='开卡表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='开卡表' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = {0})) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,'开卡表',getdate()
 from  dbo.Emp_BasicInfo emp
inner join orgforhr o on emp.orgid = o.id
     left join dbo.Emp_Duty ed on emp.id = ed.EmpbasicID and [JobStatus] = '在任'
      where emp.ID = {0}", empid);
                                if (!string.IsNullOrEmpty(chasql))
                                    AccessHelper.GetInstance().ExecuteNonQuery(chasql);
                            }
                        }


                    }
                    else
                    {
                        if (!string.IsNullOrEmpty(sql))
                            AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    }
                }
            }
            catch (Exception ex)
            {

                Log4JClass.SetErro("录用管理" + quedingzanhuan+"操作", ex);
            }
            return true;
        }

        public ObservableCollection<ZhaoLu_DaoRuKaoShengModel> ZL_GetMianShiTongZhiBiao(int exameid, DateTime? dt, string zu, string shangxiawu)
        {
            ObservableCollection<ZhaoLu_DaoRuKaoShengModel> rights = new ObservableCollection<ZhaoLu_DaoRuKaoShengModel>();
            string sql = "";
            sql =
                @"select ROW_NUMBER() OVER(ORDER BY zz.RiQi,zz.sec1,CONVERT(datetime, zz.StartShi) , z.Department,z.Duty) Sec, zz.riqi,zz.zu,(case when datepart(hh,zz.StartShi)>9 then cast(datepart(hh,zz.StartShi) as varchar(2)) else '0' + cast(datepart(hh,zz.StartShi) as varchar(2)) end + 
':' + (case when datepart(mi,zz.StartShi)>9 then cast(datepart(mi,zz.StartShi) as varchar(2)) else '0' + cast(datepart(mi,zz.StartShi) as varchar(2)) end+ 
':' + (case when datepart(ss,zz.StartShi)>9 then cast(datepart(ss,zz.StartShi) as varchar(2)) else '0' + cast(datepart(ss,zz.StartShi) as varchar(2)) end))
 + '-' + case when datepart(hh,zz.endshi)>9 then cast(datepart(hh,zz.endshi) as varchar(2)) else '0' + cast(datepart(hh,zz.endshi) as varchar(2)) end + 
':' + (case when datepart(mi,zz.endshi)>9 then cast(datepart(mi,zz.endshi) as varchar(2)) else '0' + cast(datepart(mi,zz.endshi) as varchar(2)) end+ 
':' + (case when datepart(ss,zz.endshi)>9 then cast(datepart(ss,zz.endshi) as varchar(2)) else '0' + cast(datepart(ss,zz.endshi) as varchar(2)) end))) as Shi,
o.name OrgName,z.duty,zz.didian,zdk.*
 from dbo.ZhaoLu_DaoRuKaoSheng zdk 
inner join ZL_MianShiEmpPiPei mep on zdk.ID = mep.ZhaoLu_DaoRuKaoShengID
inner join ZhaoLuZhiWei z on zdk.zwid = z.ZhiWeiDaiMa and z.ID = mep.ZhaoLuZhiWeiID
inner join ZL_ZhiWeiShiJianAnPai zz on z.id = zz.PublicOfficialSelectionID and zz.ID = mep.ZL_ZhiWeiShiJianAnPaiID
inner join orgforhr o on o.id = z.orgid                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
where z.year = " +
                exameid;
            if (dt != null) sql += " and convert(datetime,zz.riqi) = '" + dt + "'";
            if (!string.IsNullOrEmpty(zu) && zu != "全部") sql += " and zz.zu = '" + zu + "'";
            if (shangxiawu != "无")
            {
                if (shangxiawu == "上午")
                {
                    sql += " and datepart(hh,zz.startshi) <= 12";
                }
                if (shangxiawu == "下午")
                {
                    sql += " and datepart(hh,zz.startshi) >= 12";
                }
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLu_DaoRuKaoShengModel.FillEntity(dr));
            }
            return rights;
        }

        public bool InsertMianShiEmp(ObservableCollection<ZL_MianShiEmpPiPeiModel> model)
        {
            string sql = "";
            if(model != null && model.Count > 0)
            {
                sql = string.Format(@"delete from ZL_MianShiEmpPiPei where ZhaoLuZhiWeiID = {0} and ZL_ZhiWeiShiJianAnPaiID = {1}",model[0].ZhaoLuZhiWeiID,
                    model[0].ZL_ZhiWeiShiJianAnPaiID);
                foreach(ZL_MianShiEmpPiPeiModel i in model)
                {
                    sql += string.Format(@";INSERT INTO ZL_MianShiEmpPiPei
           ([ZhaoLu_DaoRuKaoShengID]
           ,[ZhaoLuZhiWeiID]
           ,[ZL_ZhiWeiShiJianAnPaiID])
     VALUES
           ({0}
           ,{1}
           ,{2})", i.ZhaoLu_DaoRuKaoShengID,i.ZhaoLuZhiWeiID,i.ZL_ZhiWeiShiJianAnPaiID);
                }
                
            }
          
            if (!string.IsNullOrEmpty(sql))
                AccessHelper.GetInstance().ExecuteNonQuery(sql);
            return true;
        }

        public ObservableCollection<ZhaoLuZhengShen_AttatchmentModel> GetZhaoLuZhengShen_Attatchment(int id)
        {
            ObservableCollection<ZhaoLuZhengShen_AttatchmentModel> rights = new ObservableCollection<ZhaoLuZhengShen_AttatchmentModel>();
            string sql = "";
            sql = string.Format(@"select * from ZhaoLuZhengShen_Attatchment where mainid = {0}", id);

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                rights.Add(ZhaoLuZhengShen_AttatchmentModel.FillEntity(dr));
            }
            return rights;
        }

        //#region 导出招录面试人员表
        public FileTransferStream ZhaoLu_GetExcelFile(ObservableCollection<ZhaoLu_DaoRuKaoShengModel> models, string username, Dictionary<string, bool> dic)
        {
            FileTransferStream ftfs = new FileTransferStream();
            if (_content != null)
            {
            }
            else
            {
                HSSFWorkbook workbook = null;
                MemoryStream ms = new MemoryStream(ResourceFile.zhaokao_mianshi);
                workbook = new HSSFWorkbook(ms);//从流内容创建Workbook对象
                ms.Dispose();
                ZhaoLuGetInfo(models, workbook, dic);
                string strFileName = AppDomain.CurrentDomain.BaseDirectory + username + ".xls";
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    workbook.Write(fs);

                }
                using (FileStream file = File.Open(strFileName, FileMode.Open))
                {
                    BinaryReader r = new BinaryReader(file);

                    r.BaseStream.Seek(0, SeekOrigin.Begin);    //将文件指针设置到文件开

                    _content = r.ReadBytes((int)r.BaseStream.Length);
                }
                if (File.Exists(strFileName))
                {
                    File.Delete(strFileName);
                }


                //{
                //    _content = new byte[file.Length];
                //    file.Write(_content, 0, _content.Length);
                //}

                ftfs.IsFinished = true;
                ftfs.FileContent = _content;

            }
            return ftfs;
        }
        //private DataSet ZhaoLuGetInfo(ObservableCollection<ZhaoLu_DaoRuKaoShengModel> models, HSSFWorkbook workbook, Dictionary<string, bool> dic)
        //{
        //    HSSFSheet sheet1 = workbook.GetSheetAt(0);
        //    HSSFCellStyle style = workbook.CreateCellStyle();
        //    style.Alignment = HSSFCellStyle.ALIGN_CENTER;
        //    style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
        //    style.BorderBottom = HSSFCellStyle.BORDER_THIN;
        //    style.BorderLeft = HSSFCellStyle.BORDER_THIN;
        //    style.BorderRight = HSSFCellStyle.BORDER_THIN;
        //    style.BorderTop = HSSFCellStyle.BORDER_THIN;
        //    #region 基本信息

        //    for (int i = 0; i < models.Count; i++)
        //    {
        //        var row = sheet1.CreateRow(i + 3);
        //        row.Height = 20 * 20;
        //        for (int ii = 0; ii < 31; ii++)
        //        {
        //            HSSFCell cell = row.CreateCell(ii);
        //            cell.CellStyle = style;
        //        }
        //        SetCellValue(sheet1, i + 4, 0, models[i].Sec.ToString());
        //        SetCellValue(sheet1, i + 4, 1, models[i].RiQi != null ? Convert.ToDateTime(models[i].RiQi).ToString("yyyy-MM-dd") : null);
        //        SetCellValue(sheet1, i + 4, 2, models[i].Zu);
        //        SetCellValue(sheet1, i + 4, 3, models[i].Shi);
        //        SetCellValue(sheet1, i + 4, 4, models[i].ZLDW);
        //        SetCellValue(sheet1, i + 4, 5, models[i].YRBM);
        //        SetCellValue(sheet1, i + 4, 6, models[i].BBZW);
        //        SetCellValue(sheet1, i + 4, 7, models[i].ZCBH.ToString());
        //        SetCellValue(sheet1, i + 4, 8, models[i].XingMing);
        //        SetCellValue(sheet1, i + 4, 9, models[i].SFZH);

        //        SetCellValue(sheet1, i + 4, 10, models[i].ZWID);
        //        SetCellValue(sheet1, i + 4, 11, models[i].ZKKM);

        //        SetCellValue(sheet1, i + 4, 12, models[i].KaoShengLeiBie);
        //        SetCellValue(sheet1, i + 4, 13, models[i].ShiFouTiaoJi);
        //        SetCellValue(sheet1, i + 4, 14, models[i].ZongFen);
        //        SetCellValue(sheet1, i + 4, 15, models[i].XB );
        //        SetCellValue(sheet1, i + 4, 16, models[i].CSRQ != null ? Convert.ToDateTime(models[i].CSRQ).ToString("yyyy-MM-dd") : null);
        //        SetCellValue(sheet1, i + 4, 17, models[i].JKCD);

        //        SetCellValue(sheet1, i + 4, 18, models[i].MZ);
        //        SetCellValue(sheet1, i + 4, 19, models[i].HYZK);
        //        SetCellValue(sheet1, i + 4, 20, models[i].KSSF);
        //        SetCellValue(sheet1, i + 4, 21, models[i].XL);
        //        SetCellValue(sheet1, i + 4, 22, models[i].ZZMM);
        //        SetCellValue(sheet1, i + 4, 23, models[i].XW);
        //        SetCellValue(sheet1, i + 4, 24, models[i].BKBYYX);
        //        SetCellValue(sheet1, i + 4, 25, models[i].BKZY);
        //        SetCellValue(sheet1, i + 4, 26, models[i].SSYX);
        //        SetCellValue(sheet1, i + 4, 27, models[i].SSZY);
        //        SetCellValue(sheet1, i + 4, 28, models[i].BSYX);
        //        SetCellValue(sheet1, i + 4, 29, models[i].BSZY);
        //        SetCellValue(sheet1, i + 4, 30, models[i].GRJL);

        //        SetCellValue(sheet1, i + 4, 31, models[i].TXDZ);
        //        SetCellValue(sheet1, i + 4, 32, models[i].LXFS);
        //        SetCellValue(sheet1, i + 4, 33, models[i].SJHM);
        //        SetCellValue(sheet1, i + 4, 34, models[i].GZDW);
        //        SetCellValue(sheet1, i + 4, 35, models[i].GZNX);
        //        SetCellValue(sheet1, i + 4, 36, models[i].SY);
        //        SetCellValue(sheet1, i + 4, 37, models[i].HJ);
        //        SetCellValue(sheet1, i + 4, 38, models[i].YB);
        //        SetCellValue(sheet1, i + 4, 39, models[i].BaoMingBeiZhu);
        //    }
        //    #endregion
        //    //hide column C
        //    if (dic["面试日期"] == false) sheet1.SetColumnHidden(1, true);
        //    if (dic["场次"] == false) sheet1.SetColumnHidden(2, true);
        //    if (dic["面试时间"] == false) sheet1.SetColumnHidden(3, true);
        //    if (dic["招录单位"] == false) sheet1.SetColumnHidden(4, true);
        //    if (dic["用人部门"] == false) sheet1.SetColumnHidden(5, true);
        //    if (dic["报考职位"] == false) sheet1.SetColumnHidden(6, true);
        //    if (dic["注册编号"] == false) sheet1.SetColumnHidden(7, true);
        //    if (dic["姓名"] == false) sheet1.SetColumnHidden(8, true);
        //    if (dic["身份证号"] == false) sheet1.SetColumnHidden(9, true);
        //    if (dic["职位ID"] == false) sheet1.SetColumnHidden(10, true);

        //    if (dic["招考科目"] == false) sheet1.SetColumnHidden(11, true);
        //    if (dic["考试类别"] == false) sheet1.SetColumnHidden(12, true);
        //    if (dic["是否调剂"] == false) sheet1.SetColumnHidden(13, true);
        //    if (dic["总分"] == false) sheet1.SetColumnHidden(14, true);
        //    if (dic["性别"] == false) sheet1.SetColumnHidden(15, true);
        //    if (dic["出生日期"] == false) sheet1.SetColumnHidden(16, true);
        //    if (dic["健康程度"] == false) sheet1.SetColumnHidden(17, true);
        //    if (dic["民族"] == false) sheet1.SetColumnHidden(18, true);
        //    if (dic["婚姻状况"] == false) sheet1.SetColumnHidden(19, true);
        //    if (dic["考生身份"] == false) sheet1.SetColumnHidden(20, true);
        //    if (dic["学历"] == false) sheet1.SetColumnHidden(21, true);

        //    if (dic["政治面貌"] == false) sheet1.SetColumnHidden(22, true);
        //    if (dic["学位"] == false) sheet1.SetColumnHidden(23, true);
        //    if (dic["本科毕业院校"] == false) sheet1.SetColumnHidden(24, true);
        //    if (dic["本科专业"] == false) sheet1.SetColumnHidden(25, true);
        //    if (dic["硕士毕业院校"] == false) sheet1.SetColumnHidden(26, true);
        //    if (dic["硕士专业"] == false) sheet1.SetColumnHidden(27, true);
        //    if (dic["博士毕业院校"] == false) sheet1.SetColumnHidden(28, true);
        //    if (dic["博士专业"] == false) sheet1.SetColumnHidden(29, true);
        //    if (dic["个人简历"] == false) sheet1.SetColumnHidden(30, true);

        //    if (dic["通讯地址"] == false) sheet1.SetColumnHidden(31, true);
        //    if (dic["联系方式"] == false) sheet1.SetColumnHidden(32, true);
        //    if (dic["手机号码"] == false) sheet1.SetColumnHidden(33, true);
        //    if (dic["工作单位"] == false) sheet1.SetColumnHidden(34, true);
        //    if (dic["工作年限"] == false) sheet1.SetColumnHidden(35, true);
        //    if (dic["生源"] == false) sheet1.SetColumnHidden(36, true);
        //    if (dic["户籍"] == false) sheet1.SetColumnHidden(37, true);
        //    if (dic["邮编"] == false) sheet1.SetColumnHidden(38, true);
        //    if (dic["报名备注信息"] == false) sheet1.SetColumnHidden(39, true);
        //    return null;
        //}
        //#endregion
    }
}
